import requests
from flask import Flask, request, jsonify, render_template, send_from_directory
import datetime
from flask_jwt_extended import create_access_token,JWTManager, get_jwt_identity, jwt_required, get_jwt
from os import path
import pymysql
from flask_cors import CORS
import random
import base64
import json
import os
import time
import hashlib


app = Flask(__name__)
CORS(app)
app.config['JWT_SECRET_KEY'] = 'sdfdsfdsf'
jwt = JWTManager()
jwt.init_app(app)

# 连接数据库
conn = pymysql.connect(
    host='116.204.102.56',       # 主机名（或IP地址）
    port=3306,                   # 端口号，默认为3306
    user='sx_item1',             # 用户名
    password='3kbrtwMn5a4Hfrkw', # 密码
    charset='utf8mb4',           # 设置字符编码
    database='sx_item1'          # 数据库名
)

# 验证用户
def verifyUser(userName, userPassword):
    # 连接数据库
    cursor = conn.cursor()
    # 查询用户是否存在
    cursor.execute(f'select count(*) from user_info where user_name = "{userName}"')
    if cursor.fetchone()[0] == 0:
        return False
    # 查询密码是否正确
    cursor.execute(f'select count(*) from user_info where user_name = "{userName}" and user_password = "{userPassword}"')
    if cursor.fetchone()[0] == 0:
        return False
    # 查询用户id
    cursor.execute(f'select Id from user_info where user_name = "{userName}"')
    Id = cursor.fetchone()[0]
    return True, Id

# 产生加密值
def generateHash(password):
    SALE=password[:4]	# 设置盐值
    md_sale=hashlib.md5((str(password).join(SALE)).encode()) 
    md5salepwd=md_sale.hexdigest()
    return md5salepwd   

@app.route('/api/getData', methods=['GET'])
def getData():
    try:
        cursor = conn.cursor()

        cursor.execute("select pl.ID, pl.poem_name, pl.poem_dynasty, pl.poem_author, "
                       "pt.topic_1, pt.topic_2, pt.topic_3, pt.topic_4, pt.topic_5 "
                       "from poem_list pl left join poem_topic pt "
                       "on pl.ID = pt.poem_id order by rand() limit 10")

        rows = cursor.fetchall()

        data = []
        poem_topics= []
        for row in rows:
            poem_data = {
                'ID': row[0],
                'poem_name': row[1],
                'poem_dynasty': row[2],
                'poem_author': row[3],
                'poem_topics': []
            }
            if row[4]:
                poem_data['poem_topics'].append(row[4])
            if row[5]:
                poem_data['poem_topics'].append(row[5])
            if row[6]:
                poem_data['poem_topics'].append(row[6])
            if row[7]:
                poem_data['poem_topics'].append(row[7])
            if row[8]:
                poem_data['poem_topics'].append(row[8])

            data.append(poem_data)

        cursor.close()
        # print(data)
        return jsonify({'data': data})

    except Exception as e:
        print(f"Error fetching data: {e}")
        return jsonify({'error': 'Error fetching data'})

@app.route('/api/register', methods=['POST'])
def register():
    try:
        data = request.get_json()
        username = data['userName']
        password = data['userPassword']
        password = generateHash(password)
        cursor = conn.cursor()
        cursor.execute("select * from user_info where user_name = %s", (username,))
        if cursor.fetchone():
            return jsonify({'error': 'User already exists'})

        cursor.execute("insert into user_info (user_name, user_password) values (%s, %s)", (username, password))
        conn.commit()
        cursor.close()
        stats, id = verifyUser(username, password)
        if stats:
            # 设置token
            access_token = create_access_token(identity=id)
            return jsonify(data={
                'message': '登录成功',
                'token': access_token
            })
        else:
            return '登录失败', 400

    except Exception as e:
        print(f"Error creating user: {e}")
        return jsonify({'error': 'Error creating user'})

@app.route('/api/login', methods=['POST'])
def login():
    userName = request.json.get('userName')
    userPassword = request.json.get('userPassword')
    userPassword = generateHash(userPassword)
    if userName == None or userPassword == None:
        return '参数错误', 400
    stats, id = verifyUser(userName, userPassword)
    if stats:
        # 设置token
        access_token = create_access_token(identity=id)


        return jsonify(data={
            'message': '登录成功',
            'token': access_token
        })
    else:
        return '登录失败', 400

#------历史记录相关操作---------
#显示错题
@app.route('/api/showHis', methods=['GET'])
@jwt_required(optional=True)
def display_history():
    user_id = get_jwt_identity()
    cursor=conn.cursor()
    # print(user_id)
    cursor.execute("SELECT * FROM user_history where user_id={} ORDER BY timestamp DESC;".format(user_id))
    rows=cursor.fetchall()#用户错误集
    history = []
    for row in rows:
        topic_id=row[2]
        # print(row)
        time = row[1]
        cursor.execute("SELECT * FROM poem_topic where ID={};".format(topic_id))
        poem=cursor.fetchone()#错误的诗句
        if poem:
            p = {
                'topic_id': poem[0],
                'poem_id': poem[1],
                'topics': [],
                'datetime': time,
            }
            p['topics'].append(poem[2])
            if poem[3]:
                p['topics'].append(poem[3])
            if poem[4]:
                p['topics'].append(poem[4])
            if poem[5]:
                p['topics'].append(poem[5])
            if poem[6]:
                p['topics'].append(poem[6])
            
            history.append(p)
    conn.commit()

    cursor.close()

    return jsonify(history), 200
    # return topic_ids, poem_ids, topic_1s, topic_2s, topic_3s, topic_4s,topic_5s
#错题插入
@app.route('/api/insHis', methods=['POST'])
@jwt_required(optional=True)
def insert_history():
    user_id = get_jwt_identity()
    topic_id = request.json.get('WA') 
    cursor = conn.cursor()
    for i in topic_id:
        # 查询是否已经存在
        cursor.execute("select count(*) from user_history where topic_id={} and user_id={} ;".format(i,user_id))
        if cursor.fetchone()[0] != 0:
            # 更新时间
            cursor.execute("delete from user_history where topic_id={} and user_id={} ;".format(i,user_id))
        cursor.execute("insert into user_history(topic_id,user_id) values({},{}) ;".format(i,user_id))
    conn.commit()
    cursor.close()
    return jsonify(data={
            'message': '提交成功'
        })

#做对了删除
@app.route('/api/delHis', methods=['get'])
@jwt_required(optional=True)
def delete_history():
    user_id = get_jwt_identity()
    topic_id = request.args.get('topic_id')

    cursor = conn.cursor()
    cursor.execute("delete from user_history where topic_id={} and user_id={} ;".format(topic_id,user_id))
    conn.commit()
    cursor.close()
    return jsonify(data={
            'message': '删除成功'
        })

#做错了更新时间
@app.route('/api/updateHis', methods=['GET'])
@jwt_required(optional=True)
def update_history():
    user_id = get_jwt_identity()
    topic_id = request.args.get('topic_id')
    
    cursor = conn.cursor()
    cursor.execute("delete from user_history where topic_id={} and user_id={} ;".format(topic_id, user_id))
    cursor.execute("insert into user_history(topic_id,user_id) values({},{}) ;".format(topic_id, user_id))
    conn.commit()
    cursor.close()
    return jsonify(data={
            'message': '更新成功'
        })

@app.route('/')
def index():
    return app.send_static_file('index.html')

# 其他页面转发给前端
@app.route('/<path:fallback>')
def fallback(fallback):       # Vue Router 的 mode 为 'hash' 时可移除该方法
    if fallback.startswith('assets/'):
        return app.send_static_file(fallback)
    else:
        return app.send_static_file('index.html')

if __name__ == '__main__':
    app.run(port='7921', host='0.0.0.0', threaded=True)

